package org.chen.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.chen.model.entity.TableStructure;
import org.chen.utils.TypeUtils;

public class DataBaseDAO {

	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	/**
	 * @author MrChen 根据数据库名和表名获取该表所有信息
	 * 
	 * @param dataBaseName
	 *            数据库名称
	 * @param tableName
	 *            表名
	 */
	public List<TableStructure> showTableStructure(String dataBaseName, String tableName) {
		List<TableStructure> columns = new ArrayList<TableStructure>();
		conn = BaseDAO.getConn();
		if (conn != null) {
			String sql = "show COLUMNS from " + dataBaseName + "." + tableName;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while (rs.next()) {
					TableStructure structure = new TableStructure();
					structure.setField(rs.getString("Field"));
					structure.setType(rs.getString("Type"));
					structure.setNull(rs.getString("Null"));
					structure.setKey(rs.getString("Key"));
					structure.setDefault(rs.getString("Default"));
					structure.setExtra(rs.getString("Extra"));
					structure.setGenerateType(TypeUtils.getGenerateTypes(rs.getString("Type")));
					columns.add(structure);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				BaseDAO.close(rs, ps, conn);
			}
		}
		return columns;
	}

	/**
	 * @author MrChen 根据数据库名称获取该库中的所有表
	 * 
	 * @param dataBaseName
	 *            数据库名称
	 */
	public List<String> showTables(String dataBaseName) {
		List<String> tables = new ArrayList<String>();
		conn = BaseDAO.getConn();
		if (conn != null) {
			String sql = "show TABLES from " + dataBaseName;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while (rs.next()) {
					tables.add(rs.getString(1));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				BaseDAO.close(rs, ps, conn);
			}
		}
		return tables;
	}

	/**
	 * @author MrChen 获取所有数据库名称
	 */
	public List<String> showDataBase() {
		List<String> databaseName = new ArrayList<String>();
		conn = BaseDAO.getConn();
		if (conn != null) {
			String sql = "show DATABASES";
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while (rs.next()) {
					databaseName.add(rs.getString(1));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				BaseDAO.close(rs, ps, conn);
			}
		}
		return databaseName;
	}

	public static void main(String[] args) {
		DataBaseDAO dao = new DataBaseDAO();
		List<TableStructure> showTableStructure = dao.showTableStructure("demo", "emp");
		for (TableStructure string : showTableStructure) {
			System.out.println(string);
		}
	}

}
